﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace QLKCB.Model
{
    class ConnectDB
    {

        public string getStrConnection()
        {
            string serverName = ConfigurationManager.AppSettings["Server"].ToString();
            string dataBase = ConfigurationManager.AppSettings["Database"].ToString();
            string userName = ConfigurationManager.AppSettings["Username"].ToString();
            string passWord = ConfigurationManager.AppSettings["Password"].ToString();
            string str = "Data Source=" + serverName + ";Initial Catalog=" + dataBase + ";Persist Security Info=True;User ID=" + userName + ";Password=" + passWord;
            return str;
        }

        public DataTable ExeQuery(string sql, params object[] pars)
        {
            SqlConnection cn = new SqlConnection();
            cn.ConnectionString = getStrConnection();
            cn.Open();

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandText = sql;

            for (int i = 0; i < pars.Length; i = i + 2)
            {
                cmd.Parameters.Add(new SqlParameter(pars[i].ToString(), pars[i + 1]));
            }

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);

            cn.Close();
            return dt;
        }

        public int ExeNonQuery(string sql, params object[] pars)
        {
            SqlConnection cn = new SqlConnection();
            cn.ConnectionString = getStrConnection();
            cn.Open();

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandText = sql;

            for (int i = 0; i < pars.Length; i = i + 2)
            {
                cmd.Parameters.Add(new SqlParameter(pars[i].ToString(), pars[i + 1]));
            }

            int result = cmd.ExecuteNonQuery();
            cn.Close();

            return result;

        }

    }
}
